{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 导包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append(\"/home/loong/jupyter\")\n",
    "import common_utils\n",
    "from common_utils import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 加载数据并进行EDA初步分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "load 290 file,data shape (36850, 25)\n",
      "(36850, 25)\n"
     ]
    }
   ],
   "source": [
    "file_paths = data_of_dir(\"raw_012_am240326\",['_1_','_2_']) # type: ignore\n",
    "raw_df = batch_load_data(file_paths)\n",
    "print(raw_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>app</th>\n",
       "      <th>id</th>\n",
       "      <th>phone</th>\n",
       "      <th>score</th>\n",
       "      <th>system</th>\n",
       "      <th>phone_number</th>\n",
       "      <th>tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AIMX</td>\n",
       "      <td>1177796036631384064</td>\n",
       "      <td>525585648411</td>\n",
       "      <td>97</td>\n",
       "      <td>AM</td>\n",
       "      <td>5585648411</td>\n",
       "      <td>fraud_phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PRC</td>\n",
       "      <td>1e03caea772d49bdaa02f17d6e628265</td>\n",
       "      <td>523319997716</td>\n",
       "      <td>96</td>\n",
       "      <td>M</td>\n",
       "      <td>3319997716</td>\n",
       "      <td>fraud_phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PRC</td>\n",
       "      <td>8d9c247d76db403889286ad07e94fe5d</td>\n",
       "      <td>526692477798</td>\n",
       "      <td>96</td>\n",
       "      <td>M</td>\n",
       "      <td>6692477798</td>\n",
       "      <td>fraud_phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PAF</td>\n",
       "      <td>355acae192584f5a8e4f9a421f2eaf21</td>\n",
       "      <td>526271480680</td>\n",
       "      <td>96</td>\n",
       "      <td>M</td>\n",
       "      <td>6271480680</td>\n",
       "      <td>fraud_phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CPO</td>\n",
       "      <td>eec09f4d5f0248a0bb0c5c4c6776b3a1</td>\n",
       "      <td>526631512253</td>\n",
       "      <td>96</td>\n",
       "      <td>YM</td>\n",
       "      <td>6631512253</td>\n",
       "      <td>fraud_phone</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    app                                id         phone  score system  \\\n",
       "0  AIMX               1177796036631384064  525585648411     97     AM   \n",
       "1   PRC  1e03caea772d49bdaa02f17d6e628265  523319997716     96      M   \n",
       "2   PRC  8d9c247d76db403889286ad07e94fe5d  526692477798     96      M   \n",
       "3   PAF  355acae192584f5a8e4f9a421f2eaf21  526271480680     96      M   \n",
       "4   CPO  eec09f4d5f0248a0bb0c5c4c6776b3a1  526631512253     96     YM   \n",
       "\n",
       "  phone_number          tag  \n",
       "0   5585648411  fraud_phone  \n",
       "1   3319997716  fraud_phone  \n",
       "2   6692477798  fraud_phone  \n",
       "3   6271480680  fraud_phone  \n",
       "4   6631512253  fraud_phone  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取大胡子的信息\n",
    "huzi_df =[]\n",
    "with open('大胡子.txt','r') as file:\n",
    "    lines = file.readlines()\n",
    "for line in lines:\n",
    "    line = line.replace(\"'\",'\"').replace(\"\\n\",'')\n",
    "    huzi_df.append(json.loads(line))\n",
    "huzi_df = pd.DataFrame.from_records(huzi_df)\n",
    "huzi_df['phone_number'] = huzi_df['phone'].map(lambda x: x[2:])\n",
    "huzi_df['tag'] = 'fraud_phone'\n",
    "huzi_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df['def_pd1'] = raw_df.apply(def_pd1_aclc,axis=1) # type: ignore\n",
    "raw_df['def_cpd'] = raw_df.apply(def_cpd_aclc,axis=1) # type: ignore\n",
    "raw_df[\"apply_week\"] = raw_df[\"apply_time\"].map(format_date2week)\n",
    "raw_df[\"loan_week\"] = raw_df[\"loan_time\"].map(format_date2week)\n",
    "raw_df[\"total\"] = \"ALL\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>def_pd1</th>\n",
       "      <th>agr_pd1</th>\n",
       "      <th>def_cpd</th>\n",
       "      <th>agr_cpd</th>\n",
       "      <th>app_order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>pd1</th>\n",
       "      <th>cpd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>loan_week</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2023.10.30~11.05</th>\n",
       "      <td>12.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>27</td>\n",
       "      <td>8</td>\n",
       "      <td>0.444444</td>\n",
       "      <td>0.444444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.11.06~11.12</th>\n",
       "      <td>84.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>340</td>\n",
       "      <td>107</td>\n",
       "      <td>0.247059</td>\n",
       "      <td>0.232353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.11.13~11.19</th>\n",
       "      <td>265.0</td>\n",
       "      <td>872.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>872.0</td>\n",
       "      <td>872</td>\n",
       "      <td>254</td>\n",
       "      <td>0.303899</td>\n",
       "      <td>0.284404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.11.20~11.26</th>\n",
       "      <td>478.0</td>\n",
       "      <td>1463.0</td>\n",
       "      <td>445.0</td>\n",
       "      <td>1463.0</td>\n",
       "      <td>1463</td>\n",
       "      <td>393</td>\n",
       "      <td>0.326726</td>\n",
       "      <td>0.304170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.11.27~12.03</th>\n",
       "      <td>810.0</td>\n",
       "      <td>2239.0</td>\n",
       "      <td>746.0</td>\n",
       "      <td>2239.0</td>\n",
       "      <td>2239</td>\n",
       "      <td>418</td>\n",
       "      <td>0.361769</td>\n",
       "      <td>0.333184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.12.04~12.10</th>\n",
       "      <td>1050.0</td>\n",
       "      <td>2485.0</td>\n",
       "      <td>971.0</td>\n",
       "      <td>2485.0</td>\n",
       "      <td>2485</td>\n",
       "      <td>450</td>\n",
       "      <td>0.422535</td>\n",
       "      <td>0.390744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.12.11~12.17</th>\n",
       "      <td>607.0</td>\n",
       "      <td>1601.0</td>\n",
       "      <td>569.0</td>\n",
       "      <td>1601.0</td>\n",
       "      <td>1601</td>\n",
       "      <td>364</td>\n",
       "      <td>0.379138</td>\n",
       "      <td>0.355403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.12.18~12.24</th>\n",
       "      <td>669.0</td>\n",
       "      <td>1605.0</td>\n",
       "      <td>616.0</td>\n",
       "      <td>1605.0</td>\n",
       "      <td>1605</td>\n",
       "      <td>339</td>\n",
       "      <td>0.416822</td>\n",
       "      <td>0.383801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.12.25~12.31</th>\n",
       "      <td>846.0</td>\n",
       "      <td>2034.0</td>\n",
       "      <td>780.0</td>\n",
       "      <td>2034.0</td>\n",
       "      <td>2034</td>\n",
       "      <td>432</td>\n",
       "      <td>0.415929</td>\n",
       "      <td>0.383481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.01~01.07</th>\n",
       "      <td>752.0</td>\n",
       "      <td>2067.0</td>\n",
       "      <td>697.0</td>\n",
       "      <td>2067.0</td>\n",
       "      <td>2067</td>\n",
       "      <td>444</td>\n",
       "      <td>0.363812</td>\n",
       "      <td>0.337204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.08~01.14</th>\n",
       "      <td>723.0</td>\n",
       "      <td>2026.0</td>\n",
       "      <td>659.0</td>\n",
       "      <td>2026.0</td>\n",
       "      <td>2026</td>\n",
       "      <td>469</td>\n",
       "      <td>0.356861</td>\n",
       "      <td>0.325271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.15~01.21</th>\n",
       "      <td>787.0</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>728.0</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>476</td>\n",
       "      <td>0.389797</td>\n",
       "      <td>0.360575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.22~01.28</th>\n",
       "      <td>685.0</td>\n",
       "      <td>1809.0</td>\n",
       "      <td>599.0</td>\n",
       "      <td>1809.0</td>\n",
       "      <td>1809</td>\n",
       "      <td>458</td>\n",
       "      <td>0.378662</td>\n",
       "      <td>0.331122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.29~02.04</th>\n",
       "      <td>828.0</td>\n",
       "      <td>2166.0</td>\n",
       "      <td>741.0</td>\n",
       "      <td>2166.0</td>\n",
       "      <td>2166</td>\n",
       "      <td>461</td>\n",
       "      <td>0.382271</td>\n",
       "      <td>0.342105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.02.05~02.11</th>\n",
       "      <td>860.0</td>\n",
       "      <td>1914.0</td>\n",
       "      <td>774.0</td>\n",
       "      <td>1914.0</td>\n",
       "      <td>1914</td>\n",
       "      <td>445</td>\n",
       "      <td>0.449321</td>\n",
       "      <td>0.404389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.02.12~02.18</th>\n",
       "      <td>721.0</td>\n",
       "      <td>1557.0</td>\n",
       "      <td>636.0</td>\n",
       "      <td>1557.0</td>\n",
       "      <td>1557</td>\n",
       "      <td>320</td>\n",
       "      <td>0.463070</td>\n",
       "      <td>0.408478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.02.19~02.25</th>\n",
       "      <td>675.0</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>629.0</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1835</td>\n",
       "      <td>412</td>\n",
       "      <td>0.367847</td>\n",
       "      <td>0.342779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.02.26~03.03</th>\n",
       "      <td>759.0</td>\n",
       "      <td>2144.0</td>\n",
       "      <td>698.0</td>\n",
       "      <td>2144.0</td>\n",
       "      <td>2144</td>\n",
       "      <td>473</td>\n",
       "      <td>0.354011</td>\n",
       "      <td>0.325560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.03.04~03.10</th>\n",
       "      <td>816.0</td>\n",
       "      <td>2173.0</td>\n",
       "      <td>760.0</td>\n",
       "      <td>2173.0</td>\n",
       "      <td>2173</td>\n",
       "      <td>509</td>\n",
       "      <td>0.375518</td>\n",
       "      <td>0.349747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.03.11~03.17</th>\n",
       "      <td>970.0</td>\n",
       "      <td>2309.0</td>\n",
       "      <td>919.0</td>\n",
       "      <td>2309.0</td>\n",
       "      <td>2309</td>\n",
       "      <td>515</td>\n",
       "      <td>0.420095</td>\n",
       "      <td>0.398008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.03.18~03.24</th>\n",
       "      <td>218.0</td>\n",
       "      <td>578.0</td>\n",
       "      <td>372.0</td>\n",
       "      <td>907.0</td>\n",
       "      <td>1894</td>\n",
       "      <td>481</td>\n",
       "      <td>0.377163</td>\n",
       "      <td>0.410143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.03.25~03.31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>271</td>\n",
       "      <td>117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  def_pd1  agr_pd1  def_cpd  agr_cpd  app_order_id  user_id  \\\n",
       "loan_week                                                                     \n",
       "2023.10.30~11.05     12.0     27.0     12.0     27.0            27        8   \n",
       "2023.11.06~11.12     84.0    340.0     79.0    340.0           340      107   \n",
       "2023.11.13~11.19    265.0    872.0    248.0    872.0           872      254   \n",
       "2023.11.20~11.26    478.0   1463.0    445.0   1463.0          1463      393   \n",
       "2023.11.27~12.03    810.0   2239.0    746.0   2239.0          2239      418   \n",
       "2023.12.04~12.10   1050.0   2485.0    971.0   2485.0          2485      450   \n",
       "2023.12.11~12.17    607.0   1601.0    569.0   1601.0          1601      364   \n",
       "2023.12.18~12.24    669.0   1605.0    616.0   1605.0          1605      339   \n",
       "2023.12.25~12.31    846.0   2034.0    780.0   2034.0          2034      432   \n",
       "2024.01.01~01.07    752.0   2067.0    697.0   2067.0          2067      444   \n",
       "2024.01.08~01.14    723.0   2026.0    659.0   2026.0          2026      469   \n",
       "2024.01.15~01.21    787.0   2019.0    728.0   2019.0          2019      476   \n",
       "2024.01.22~01.28    685.0   1809.0    599.0   1809.0          1809      458   \n",
       "2024.01.29~02.04    828.0   2166.0    741.0   2166.0          2166      461   \n",
       "2024.02.05~02.11    860.0   1914.0    774.0   1914.0          1914      445   \n",
       "2024.02.12~02.18    721.0   1557.0    636.0   1557.0          1557      320   \n",
       "2024.02.19~02.25    675.0   1835.0    629.0   1835.0          1835      412   \n",
       "2024.02.26~03.03    759.0   2144.0    698.0   2144.0          2144      473   \n",
       "2024.03.04~03.10    816.0   2173.0    760.0   2173.0          2173      509   \n",
       "2024.03.11~03.17    970.0   2309.0    919.0   2309.0          2309      515   \n",
       "2024.03.18~03.24    218.0    578.0    372.0    907.0          1894      481   \n",
       "2024.03.25~03.31      0.0      0.0      0.0      0.0           271      117   \n",
       "\n",
       "                       pd1       cpd  \n",
       "loan_week                             \n",
       "2023.10.30~11.05  0.444444  0.444444  \n",
       "2023.11.06~11.12  0.247059  0.232353  \n",
       "2023.11.13~11.19  0.303899  0.284404  \n",
       "2023.11.20~11.26  0.326726  0.304170  \n",
       "2023.11.27~12.03  0.361769  0.333184  \n",
       "2023.12.04~12.10  0.422535  0.390744  \n",
       "2023.12.11~12.17  0.379138  0.355403  \n",
       "2023.12.18~12.24  0.416822  0.383801  \n",
       "2023.12.25~12.31  0.415929  0.383481  \n",
       "2024.01.01~01.07  0.363812  0.337204  \n",
       "2024.01.08~01.14  0.356861  0.325271  \n",
       "2024.01.15~01.21  0.389797  0.360575  \n",
       "2024.01.22~01.28  0.378662  0.331122  \n",
       "2024.01.29~02.04  0.382271  0.342105  \n",
       "2024.02.05~02.11  0.449321  0.404389  \n",
       "2024.02.12~02.18  0.463070  0.408478  \n",
       "2024.02.19~02.25  0.367847  0.342779  \n",
       "2024.02.26~03.03  0.354011  0.325560  \n",
       "2024.03.04~03.10  0.375518  0.349747  \n",
       "2024.03.11~03.17  0.420095  0.398008  \n",
       "2024.03.18~03.24  0.377163  0.410143  \n",
       "2024.03.25~03.31       NaN       NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group = [\"loan_week\"]\n",
    "sum_col = [\"def_pd1\", \"agr_pd1\", \"def_cpd\", \"agr_cpd\"]\n",
    "count_col = [\"app_order_id\"]\n",
    "unique_col = [\"user_id\"]\n",
    "rate_tupes = [(\"def_pd1\", \"agr_pd1\", \"pd1\"), (\"def_cpd\", \"agr_cpd\", \"cpd\")]\n",
    "group_calc(raw_df, group, sum_col, count_col, unique_col, rate_tupes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AIMX' 'ATCC' 'ARIP']\n",
      "['AIMX' 'APTF' 'ARPP' 'AEIV' 'ADOA' 'AFCH' 'ATCC' 'ADKB' 'ASOL' 'ARIP'\n",
      " 'ACOA' 'AIDO' 'AAGI' 'ACTO' 'ANEX']\n"
     ]
    }
   ],
   "source": [
    "print(raw_df['acq_channel'].unique())\n",
    "print(raw_df['product_set_code'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(7862, 28)\n"
     ]
    }
   ],
   "source": [
    "# 老客产品建模过程仅保留主产品\n",
    "condition = raw_df['product_set_code'].isin(['AIMX', 'ATCC', 'ARIP'])  & (raw_df['applist']!='') &  raw_df['applist'].notnull() & (~raw_df['phone_number'].isin(huzi_df['phone_number']))\n",
    "data_df = raw_df[condition]\n",
    "print(data_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>def_pd1</th>\n",
       "      <th>agr_pd1</th>\n",
       "      <th>def_cpd</th>\n",
       "      <th>agr_cpd</th>\n",
       "      <th>app_order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>pd1</th>\n",
       "      <th>cpd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>loan_week</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2023.10.30~11.05</th>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.11.06~11.12</th>\n",
       "      <td>28.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>113</td>\n",
       "      <td>100</td>\n",
       "      <td>0.247788</td>\n",
       "      <td>0.230088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.11.13~11.19</th>\n",
       "      <td>89.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>292</td>\n",
       "      <td>240</td>\n",
       "      <td>0.304795</td>\n",
       "      <td>0.287671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.11.20~11.26</th>\n",
       "      <td>115.0</td>\n",
       "      <td>392.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>392.0</td>\n",
       "      <td>392</td>\n",
       "      <td>325</td>\n",
       "      <td>0.293367</td>\n",
       "      <td>0.275510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.11.27~12.03</th>\n",
       "      <td>139.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>404</td>\n",
       "      <td>337</td>\n",
       "      <td>0.344059</td>\n",
       "      <td>0.319307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.12.04~12.10</th>\n",
       "      <td>171.0</td>\n",
       "      <td>414.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>414.0</td>\n",
       "      <td>414</td>\n",
       "      <td>347</td>\n",
       "      <td>0.413043</td>\n",
       "      <td>0.367150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.12.11~12.17</th>\n",
       "      <td>105.0</td>\n",
       "      <td>301.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>301.0</td>\n",
       "      <td>301</td>\n",
       "      <td>250</td>\n",
       "      <td>0.348837</td>\n",
       "      <td>0.325581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.12.18~12.24</th>\n",
       "      <td>126.0</td>\n",
       "      <td>311.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>311.0</td>\n",
       "      <td>311</td>\n",
       "      <td>269</td>\n",
       "      <td>0.405145</td>\n",
       "      <td>0.366559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023.12.25~12.31</th>\n",
       "      <td>131.0</td>\n",
       "      <td>356.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>356.0</td>\n",
       "      <td>356</td>\n",
       "      <td>304</td>\n",
       "      <td>0.367978</td>\n",
       "      <td>0.331461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.01~01.07</th>\n",
       "      <td>112.0</td>\n",
       "      <td>335.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>335.0</td>\n",
       "      <td>335</td>\n",
       "      <td>277</td>\n",
       "      <td>0.334328</td>\n",
       "      <td>0.322388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.08~01.14</th>\n",
       "      <td>95.0</td>\n",
       "      <td>269.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>269.0</td>\n",
       "      <td>269</td>\n",
       "      <td>225</td>\n",
       "      <td>0.353160</td>\n",
       "      <td>0.304833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.15~01.21</th>\n",
       "      <td>138.0</td>\n",
       "      <td>359.0</td>\n",
       "      <td>123.0</td>\n",
       "      <td>359.0</td>\n",
       "      <td>359</td>\n",
       "      <td>254</td>\n",
       "      <td>0.384401</td>\n",
       "      <td>0.342618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.22~01.28</th>\n",
       "      <td>129.0</td>\n",
       "      <td>384.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>384.0</td>\n",
       "      <td>384</td>\n",
       "      <td>270</td>\n",
       "      <td>0.335938</td>\n",
       "      <td>0.286458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.01.29~02.04</th>\n",
       "      <td>180.0</td>\n",
       "      <td>485.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>485.0</td>\n",
       "      <td>485</td>\n",
       "      <td>319</td>\n",
       "      <td>0.371134</td>\n",
       "      <td>0.325773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.02.05~02.11</th>\n",
       "      <td>200.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>469</td>\n",
       "      <td>351</td>\n",
       "      <td>0.426439</td>\n",
       "      <td>0.375267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.02.12~02.18</th>\n",
       "      <td>171.0</td>\n",
       "      <td>376.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>376.0</td>\n",
       "      <td>376</td>\n",
       "      <td>269</td>\n",
       "      <td>0.454787</td>\n",
       "      <td>0.396277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.02.19~02.25</th>\n",
       "      <td>147.0</td>\n",
       "      <td>456.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>456.0</td>\n",
       "      <td>456</td>\n",
       "      <td>347</td>\n",
       "      <td>0.322368</td>\n",
       "      <td>0.307018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.02.26~03.03</th>\n",
       "      <td>177.0</td>\n",
       "      <td>498.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>498.0</td>\n",
       "      <td>498</td>\n",
       "      <td>375</td>\n",
       "      <td>0.355422</td>\n",
       "      <td>0.323293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.03.04~03.10</th>\n",
       "      <td>190.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>179.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>510</td>\n",
       "      <td>380</td>\n",
       "      <td>0.372549</td>\n",
       "      <td>0.350980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.03.11~03.17</th>\n",
       "      <td>232.0</td>\n",
       "      <td>550.0</td>\n",
       "      <td>224.0</td>\n",
       "      <td>550.0</td>\n",
       "      <td>550</td>\n",
       "      <td>412</td>\n",
       "      <td>0.421818</td>\n",
       "      <td>0.407273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.03.18~03.24</th>\n",
       "      <td>47.0</td>\n",
       "      <td>134.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>499</td>\n",
       "      <td>383</td>\n",
       "      <td>0.350746</td>\n",
       "      <td>0.406863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024.03.25~03.31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>81</td>\n",
       "      <td>78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  def_pd1  agr_pd1  def_cpd  agr_cpd  app_order_id  user_id  \\\n",
       "loan_week                                                                     \n",
       "2023.10.30~11.05      4.0      8.0      4.0      8.0             8        8   \n",
       "2023.11.06~11.12     28.0    113.0     26.0    113.0           113      100   \n",
       "2023.11.13~11.19     89.0    292.0     84.0    292.0           292      240   \n",
       "2023.11.20~11.26    115.0    392.0    108.0    392.0           392      325   \n",
       "2023.11.27~12.03    139.0    404.0    129.0    404.0           404      337   \n",
       "2023.12.04~12.10    171.0    414.0    152.0    414.0           414      347   \n",
       "2023.12.11~12.17    105.0    301.0     98.0    301.0           301      250   \n",
       "2023.12.18~12.24    126.0    311.0    114.0    311.0           311      269   \n",
       "2023.12.25~12.31    131.0    356.0    118.0    356.0           356      304   \n",
       "2024.01.01~01.07    112.0    335.0    108.0    335.0           335      277   \n",
       "2024.01.08~01.14     95.0    269.0     82.0    269.0           269      225   \n",
       "2024.01.15~01.21    138.0    359.0    123.0    359.0           359      254   \n",
       "2024.01.22~01.28    129.0    384.0    110.0    384.0           384      270   \n",
       "2024.01.29~02.04    180.0    485.0    158.0    485.0           485      319   \n",
       "2024.02.05~02.11    200.0    469.0    176.0    469.0           469      351   \n",
       "2024.02.12~02.18    171.0    376.0    149.0    376.0           376      269   \n",
       "2024.02.19~02.25    147.0    456.0    140.0    456.0           456      347   \n",
       "2024.02.26~03.03    177.0    498.0    161.0    498.0           498      375   \n",
       "2024.03.04~03.10    190.0    510.0    179.0    510.0           510      380   \n",
       "2024.03.11~03.17    232.0    550.0    224.0    550.0           550      412   \n",
       "2024.03.18~03.24     47.0    134.0     83.0    204.0           499      383   \n",
       "2024.03.25~03.31      0.0      0.0      0.0      0.0            81       78   \n",
       "\n",
       "                       pd1       cpd  \n",
       "loan_week                             \n",
       "2023.10.30~11.05  0.500000  0.500000  \n",
       "2023.11.06~11.12  0.247788  0.230088  \n",
       "2023.11.13~11.19  0.304795  0.287671  \n",
       "2023.11.20~11.26  0.293367  0.275510  \n",
       "2023.11.27~12.03  0.344059  0.319307  \n",
       "2023.12.04~12.10  0.413043  0.367150  \n",
       "2023.12.11~12.17  0.348837  0.325581  \n",
       "2023.12.18~12.24  0.405145  0.366559  \n",
       "2023.12.25~12.31  0.367978  0.331461  \n",
       "2024.01.01~01.07  0.334328  0.322388  \n",
       "2024.01.08~01.14  0.353160  0.304833  \n",
       "2024.01.15~01.21  0.384401  0.342618  \n",
       "2024.01.22~01.28  0.335938  0.286458  \n",
       "2024.01.29~02.04  0.371134  0.325773  \n",
       "2024.02.05~02.11  0.426439  0.375267  \n",
       "2024.02.12~02.18  0.454787  0.396277  \n",
       "2024.02.19~02.25  0.322368  0.307018  \n",
       "2024.02.26~03.03  0.355422  0.323293  \n",
       "2024.03.04~03.10  0.372549  0.350980  \n",
       "2024.03.11~03.17  0.421818  0.407273  \n",
       "2024.03.18~03.24  0.350746  0.406863  \n",
       "2024.03.25~03.31       NaN       NaN  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group = [\"loan_week\"]\n",
    "sum_col = [\"def_pd1\", \"agr_pd1\", \"def_cpd\", \"agr_cpd\"]\n",
    "count_col = [\"app_order_id\"]\n",
    "unique_col = [\"user_id\"]\n",
    "rate_tupes = [(\"def_pd1\", \"agr_pd1\", \"pd1\"), (\"def_cpd\", \"agr_cpd\", \"cpd\")]\n",
    "group_calc(data_df, group, sum_col, count_col, unique_col, rate_tupes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>def_pd1</th>\n",
       "      <th>agr_pd1</th>\n",
       "      <th>def_cpd</th>\n",
       "      <th>agr_cpd</th>\n",
       "      <th>app_order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>pd1</th>\n",
       "      <th>cpd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ALL</th>\n",
       "      <td>13615.0</td>\n",
       "      <td>35263.0</td>\n",
       "      <td>12678.0</td>\n",
       "      <td>35592.0</td>\n",
       "      <td>36850</td>\n",
       "      <td>3909</td>\n",
       "      <td>0.386099</td>\n",
       "      <td>0.356204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       def_pd1  agr_pd1  def_cpd  agr_cpd  app_order_id  user_id       pd1  \\\n",
       "total                                                                        \n",
       "ALL    13615.0  35263.0  12678.0  35592.0         36850     3909  0.386099   \n",
       "\n",
       "            cpd  \n",
       "total            \n",
       "ALL    0.356204  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group = [\"total\"]\n",
    "group_calc(raw_df, group, sum_col, count_col, unique_col, rate_tupes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>def_pd1</th>\n",
       "      <th>agr_pd1</th>\n",
       "      <th>def_cpd</th>\n",
       "      <th>agr_cpd</th>\n",
       "      <th>app_order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>pd1</th>\n",
       "      <th>cpd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ALL</th>\n",
       "      <td>2726.0</td>\n",
       "      <td>7416.0</td>\n",
       "      <td>2526.0</td>\n",
       "      <td>7486.0</td>\n",
       "      <td>7862</td>\n",
       "      <td>3334</td>\n",
       "      <td>0.367584</td>\n",
       "      <td>0.33743</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       def_pd1  agr_pd1  def_cpd  agr_cpd  app_order_id  user_id       pd1  \\\n",
       "total                                                                        \n",
       "ALL     2726.0   7416.0   2526.0   7486.0          7862     3334  0.367584   \n",
       "\n",
       "           cpd  \n",
       "total           \n",
       "ALL    0.33743  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group = [\"total\"]\n",
    "group_calc(data_df, group, sum_col, count_col, unique_col, rate_tupes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>def_pd1</th>\n",
       "      <th>agr_pd1</th>\n",
       "      <th>def_cpd</th>\n",
       "      <th>agr_cpd</th>\n",
       "      <th>app_order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>pd1</th>\n",
       "      <th>cpd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_set_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AIMX</th>\n",
       "      <td>2284.0</td>\n",
       "      <td>6264.0</td>\n",
       "      <td>2117.0</td>\n",
       "      <td>6315.0</td>\n",
       "      <td>6626</td>\n",
       "      <td>3151</td>\n",
       "      <td>0.364623</td>\n",
       "      <td>0.335234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ATCC</th>\n",
       "      <td>442.0</td>\n",
       "      <td>1152.0</td>\n",
       "      <td>409.0</td>\n",
       "      <td>1171.0</td>\n",
       "      <td>1236</td>\n",
       "      <td>677</td>\n",
       "      <td>0.383681</td>\n",
       "      <td>0.349274</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  def_pd1  agr_pd1  def_cpd  agr_cpd  app_order_id  user_id  \\\n",
       "product_set_code                                                              \n",
       "AIMX               2284.0   6264.0   2117.0   6315.0          6626     3151   \n",
       "ATCC                442.0   1152.0    409.0   1171.0          1236      677   \n",
       "\n",
       "                       pd1       cpd  \n",
       "product_set_code                      \n",
       "AIMX              0.364623  0.335234  \n",
       "ATCC              0.383681  0.349274  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group_calc(data_df, ['product_set_code'], sum_col, count_col, unique_col, rate_tupes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 开始分析各种app中对应的贷后情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "applit_data_column='applist_data'\n",
    "id_column='app_order_id'\n",
    "end_time_column='sms_upload_time'\n",
    "user_apps= parse_json_data(data_df,applit_data_column,id_column,end_time_column)\n",
    "\n",
    "user_apps['app_name'] = user_apps['app_name'].str.lower().str.strip()\n",
    "user_apps['app_package'] = user_apps['app_package'].str.lower().str.strip().str.replace(' ', '')\n",
    "user_apps['isSystem']  = user_apps['app_package'].map(lambda x :1 if 'com.apple' in x else 0 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_apps = user_apps.merge(data_df,on = 'app_order_id',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group_calc(user_apps, ['app_name','app_package'], sum_col, count_col, unique_col, rate_tupes).reset_index().to_excel(\"app贷后排序性240326.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "app_level_df= group_calc(user_apps, ['app_name','app_package'], sum_col, count_col, unique_col, rate_tupes).reset_index()\n",
    "app_level_df['r_level']='L0'\n",
    "level1_condition = (app_level_df['agr_pd1']>=4) & (app_level_df['pd1']>=0.5)\n",
    "level2_condition = (app_level_df['agr_pd1']>=4) & (app_level_df['pd1']<0.5) & (app_level_df['pd1']>=0.4)\n",
    "level3_condition = (app_level_df['agr_pd1']>=4) & (app_level_df['pd1']<0.4) & (app_level_df['pd1']>=0.3)\n",
    "level4_condition = (app_level_df['agr_pd1']>=4) & (app_level_df['pd1']<0.3)\n",
    "app_level_df.loc[level1_condition,'r_level']='L1'\n",
    "app_level_df.loc[level2_condition,'r_level']='L2'\n",
    "app_level_df.loc[level3_condition,'r_level']='L3'\n",
    "app_level_df.loc[level4_condition,'r_level']='L4'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>r_level</th>\n",
       "      <th>def_pd1</th>\n",
       "      <th>agr_pd1</th>\n",
       "      <th>def_cpd</th>\n",
       "      <th>agr_cpd</th>\n",
       "      <th>pd1</th>\n",
       "      <th>cpd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L0</td>\n",
       "      <td>1418.0</td>\n",
       "      <td>3263.0</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>3320.0</td>\n",
       "      <td>0.434569</td>\n",
       "      <td>0.387651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L1</td>\n",
       "      <td>2149.0</td>\n",
       "      <td>3720.0</td>\n",
       "      <td>1941.0</td>\n",
       "      <td>3758.0</td>\n",
       "      <td>0.577688</td>\n",
       "      <td>0.516498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L2</td>\n",
       "      <td>7045.0</td>\n",
       "      <td>16485.0</td>\n",
       "      <td>6540.0</td>\n",
       "      <td>16658.0</td>\n",
       "      <td>0.427358</td>\n",
       "      <td>0.392604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L3</td>\n",
       "      <td>249166.0</td>\n",
       "      <td>686511.0</td>\n",
       "      <td>230259.0</td>\n",
       "      <td>693273.0</td>\n",
       "      <td>0.362945</td>\n",
       "      <td>0.332133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L4</td>\n",
       "      <td>5531.0</td>\n",
       "      <td>24173.0</td>\n",
       "      <td>4921.0</td>\n",
       "      <td>24424.0</td>\n",
       "      <td>0.228809</td>\n",
       "      <td>0.201482</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  r_level   def_pd1   agr_pd1   def_cpd   agr_cpd       pd1       cpd\n",
       "0      L0    1418.0    3263.0    1287.0    3320.0  0.434569  0.387651\n",
       "1      L1    2149.0    3720.0    1941.0    3758.0  0.577688  0.516498\n",
       "2      L2    7045.0   16485.0    6540.0   16658.0  0.427358  0.392604\n",
       "3      L3  249166.0  686511.0  230259.0  693273.0  0.362945  0.332133\n",
       "4      L4    5531.0   24173.0    4921.0   24424.0  0.228809  0.201482"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group = [\"loan_week\"]\n",
    "sum_col = [\"def_pd1\", \"agr_pd1\", \"def_cpd\", \"agr_cpd\"]\n",
    "count_col = None\n",
    "unique_col = None # ['app_order_id',\"user_id\"]\n",
    "rate_tupes = [(\"def_pd1\", \"agr_pd1\", \"pd1\"), (\"def_cpd\", \"agr_cpd\", \"cpd\")]\n",
    "group_calc(app_level_df, ['r_level'], sum_col, count_col, unique_col, rate_tupes).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "app_level_df.to_excel(\"app_r_level.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5130.0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "42750*60/500"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py311",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
